package com.roc.web.action.profit;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.DateTime;

public class ProfitDetailQuerySearchCondition extends BaseSearchCondition{

	private String beginTime;
	private String endTime;
	private long id;
	private String mchtCd;//商户号
	protected String getOrderPartOfHQL() {
		return " order by ct.stlm_date ";
	}



	@Override
	protected String getRowCountHQL() {
		
		
		if(Constance.isEmpty(beginTime)||Constance.isEmpty(endTime)){
			String date = DateTime.getDay(DateTime.getNow("yyyyMMdd"), "yyyyMMdd", -1);
			beginTime=date;	
			endTime=date;
		}
		
		StringBuffer sb = new StringBuffer();
		 sb.append( "select count (1)                                                          ");
		 sb.append( "  from tbl_mer_agent_relation tp,                                         ");
	     sb.append( "       tbl_clear_txn ct                                                   ");
	     sb.append( " where  tp.agentid=    " +id                                               );
	     sb.append( "   and Translate(ct.mcht_cd USING CHAR_CS) =tp.merno                      ");
	     if(Constance.isNotEmpty(mchtCd)){
	        sb.append("          and tp.merno ='"+mchtCd+"'");//拼接查询条件
	      }
	     sb.append( "   and tp.ins_no = '48502000'                                     ");
	     sb.append("    and ct.stlm_date >='"+beginTime+"' and ct.stlm_date<='"+endTime+"'"     );//拼接查询条件
	     sb.append( "   and substr(ct.remark4, 6, 4) in('2031', '1031', '1011', '2011', '1171', '3011','1191','1291','1391','1491','1591','1691')");
		return sb.toString();

	}


	protected String getSearchHQL() {
		if(Constance.isEmpty(beginTime)||Constance.isEmpty(endTime)){
			String date = DateTime.getDay(DateTime.getNow("yyyyMMdd"), "yyyyMMdd", -1);
			beginTime=date;	
			endTime=date;
		}
		
		StringBuffer sb = new StringBuffer();
		 sb.append( "select tp.agentname,                                                      ");
	     sb.append( "       tp.merno,                                                          ");
	     sb.append( "       tp.mername,                                                        ");
	     sb.append( "       ct.card_kind_dis,                                                  ");
	     sb.append( "       ct.txn_desc,                                                       ");
	     sb.append( "       ct.trans_amt,                                                      ");
	     sb.append( "       ct.true_fee_ffd,                                                   ");
	     sb.append( "       ct.mcht_fee,                                                       ");
	     sb.append( "       ct.trans_date_time,                                                ");
	     sb.append( "       ct.pan,                                                            ");
	     sb.append( "       ct.key_rsp, ct.term_id ,ct.stlm_date ,                             ");
	     sb.append("    nvl((select m.group_cd                                   \n");
	     sb.append("                   from apsmgm.tbl_mcht_inf m                \n");
	     sb.append("                  where m.mcht_cd = tp.merno),               \n");
	     sb.append("                 'a') group_cd,                              \n");
	     sb.append("             nvl((select f.mcht_fee_pct_max                      \n");
	     sb.append("                from apsmgm.tbl_mcht_biz_fee f               \n");
	     sb.append("               where f.mcht_cd = tp.merno                    \n");
	     sb.append("                 and f.prod_cd = '1171'                      \n");
	     sb.append("                 and f.sub_biz_cd = '0002000'),0) mer_max,      \n");
	     sb.append("             nvl((select f.mcht_a_fee_percent                    \n");
	     sb.append("                from apsmgm.tbl_mcht_biz_fee f               \n");
	     sb.append("               where f.mcht_cd = tp.merno                    \n");
	     sb.append("                 and f.prod_cd = '1171'                      \n");
	     sb.append("                 and f.sub_biz_cd = '0002000'),0) mer_fwf       \n");
	     sb.append( "  from tbl_mer_agent_relation tp,                                         ");
	     sb.append( "       tbl_clear_txn ct                                                   ");
	     sb.append( " where  tp.agentid=    " +id                                               );
	     sb.append( "   and Translate(ct.mcht_cd USING CHAR_CS) =tp.merno                      ");
	     if(Constance.isNotEmpty(mchtCd)){
	        sb.append("          and tp.merno ='"+mchtCd+"'");//拼接查询条件
	      }
	     sb.append( "   and tp.ins_no = '48502000'                                     ");
	     sb.append("    and ct.stlm_date >='"+beginTime+"' and ct.stlm_date<='"+endTime+"'"     );//拼接查询条件
 
	     sb.append( "   and substr(ct.remark4, 6, 4) in('2031', '1031', '1011', '2011', '1171', '3011','1191','1291','1391','1491','1591','1691')");
		return sb.toString();
	}
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();

		return filedBeanList;
	}


	public String getBeginTime() {
		return beginTime;
	}


	public void setBeginTime(String beginTime) {
		this.beginTime = beginTime;
	}


	public String getEndTime() {
		return endTime;
	}


	public void setEndTime(String endTime) {
		this.endTime = endTime;
	}


	public long getId() {
		return id;
	}


	public void setId(long id) {
		this.id = id;
	}



	public String getMchtCd() {
		return mchtCd;
	}



	public void setMchtCd(String mchtCd) {
		this.mchtCd = mchtCd;
	}




}
